﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace YunLib.DAL
{
    internal class DB
    {
        /// <summary>
        /// 默认连接字符串
        /// </summary>
        protected static string ConnectionString = ConfigurationManager.ConnectionStrings["DBConn"].ConnectionString;

        /// <summary>
        /// 默认连接
        /// </summary>
        protected static SqlConnection DbConnection
        {
            get
            {
                var connection = new SqlConnection(ConnectionString);
                try
                {
                    connection.Open();

                    return connection; 
                }
                catch (Exception ex)
                {
                    throw new InvalidOperationException("打开数据库时发生错误，请保证数据库可以正常连接，确保web.config中ConnectionString正确配置。\n详细错误：" + ex.Message);
                }
            }
        }

         /// <summary> 
        /// 执行增、删、改方法 
        /// </summary> 
        /// <param name="sql"></param> 
        /// <param name="parms"></param> 
        /// <returns></returns> 
        public static int ExecuteNonQuery(string sql, object parms = null) 
        {
            using (IDbConnection conn = DbConnection) 
            { 
                return conn.Execute(sql,parms); 
            } 
        } 

        /// <summary> 
        /// 得到单行单列 
        /// </summary> 
        /// <param name="sql"></param> 
        /// <param name="parms"></param> 
        /// <returns></returns> 
        public static T ExecuteScalar<T>(string sql, object parms = null, Func<object, T> convertTo = null) 
        {
            using (IDbConnection conn = DbConnection) 
            {
                return conn.ExecuteScalar<T>(sql, parms, convertTo); 
            } 
        } 

         /// <summary> 
        /// 单个数据集查询 
        /// </summary> 
        /// <param name="sql"></param> 
        /// <param name="parms"></param> 
        /// <returns></returns> 
        public static List<TEntity> Query<TEntity>(string sql,Func<TEntity,bool> pre ,object parms = null) 
        {
            using (IDbConnection conn = DbConnection) 
            { 
                return conn.Query<TEntity>(sql, parms).Where(pre).ToList(); 
            } 
        } 
 
        /// <summary> 
        /// 单个数据集查询 
        /// </summary> 
        /// <param name="sql"></param> 
        /// <param name="parms"></param> 
        /// <returns></returns> 
        public static List<TEntity> Query<TEntity>(string sql, object parms = null) 
        {
            using (IDbConnection conn = DbConnection) 
            { 
                return conn.Query<TEntity>(sql, parms).ToList(); 
            } 
        }    
 
        /// <summary> 
        /// 多个数据集查询 
        /// </summary> 
        /// <param name="sql"></param> 
        /// <param name="parms"></param> 
        /// <returns></returns> 
        public static SqlMapper.GridReader MultyQuery(string sql, object parms = null) 
        {
            return DbConnection.QueryMultiple(sql, parms);
        }

        /// <summary> 
        /// 一数据集查询 
        /// </summary> 
        /// <param name="sql"></param> 
        /// <param name="parms"></param> 
        /// <returns></returns> 
        public static DataSet MultyQueryReturnDataSet(string sql, object parms = null,IDbTransaction transaction = null, int? commandTimeout = null)
        {
            using (IDbConnection conn = DbConnection)
            {
                return conn.MultyQueryReturnDataSet(sql, parms,transaction,commandTimeout);
            }
        } 
 
        /// <summary> 
        /// 单个数据集查询 
        /// </summary> 
        /// <param name="sql"></param> 
        /// <param name="parms"></param> 
        /// <returns></returns> 
        public static TEntity FirstOrDefault<TEntity>(string sql,Func<TEntity,bool> selector, object parms = null) 
        {
            using (IDbConnection conn = DbConnection) 
            { 
                return conn.Query<TEntity>(sql, parms).Where(selector).FirstOrDefault(); 
            } 
        }

        /// <summary>
        /// 事务处理
        /// </summary>
        /// <param name="Params"></param>
        public static bool ExecuteWithTransaction(List<TransactionItem> Params)
        {
            if (Params == null || Params.Count == 0) return false;

            using (IDbConnection conn = DbConnection)
            {
                //开始事务
                IDbTransaction transaction = conn.BeginTransaction();
                try
                {
                    for (int i = 0, l = Params.Count; i < l; i++)
                        conn.Execute(Params[i].SqlText, Params[i].Model, transaction);

                    //提交事务
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    //出现异常，事务Rollback
                    transaction.Rollback();
                    throw new Exception(ex.Message);
                }

                return true;
            } 
        }

        public static List<T> ExecuteStoredProcedureReturnList<T>(string storedProcedureName, object args)
        {
            using (IDbConnection con = DbConnection)
            {
                List<T> list = new List<T>();
                list = con.Query<T>(storedProcedureName,
                                        args,
                                        null,
                                        true,
                                        null,
                                        CommandType.StoredProcedure).ToList();
                return list;
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="storedProcedureName"></param>
        /// <returns></returns>
        public static T ExecuteStoredProcedureReturnSingle<T>(string storedProcedureName, DynamicParameters args,string outputParamName)
        {
            //DynamicParameters dp = new DynamicParameters();
            //dp.Add("@RoleId", "1");
            //dp.Add("@RoleName", "", DbType.String, ParameterDirection.Output);
            using (IDbConnection con = DbConnection)
            {
                con.Execute(storedProcedureName, args, null, null, CommandType.StoredProcedure);
                T value = args.Get<T>(outputParamName);
                return value;
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="storedProcedureName"></param>
        ///// <returns></returns>
        //public static int ExecuteStoredProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        //{
        //    using (SqlConnection con = DbConnection)
        //    {
        //        return con.ExecuteStoredProcedureRetuenValue(storedProcName, parameters, out rowsAffected);
        //    }
        //}

        /// <summary>
        /// 执行存储过程返回结果集
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="storedProcedureName"></param>
        /// <returns></returns>
        public static DataSet ExecuteStoredProcedure(string storedProcedureName, object args)
        {
            using (IDbConnection con = DbConnection)
            {
                return con.ExecuteStoredProcedure(storedProcedureName, args);
            }
        }

        static public IDataReader GetRS(String Sql)
        {
            using (SqlCommand cmd = new SqlCommand(Sql, DbConnection))
            {
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }
    }

    /// <summary>
    /// 执行事务项
    /// </summary>
    public class TransactionItem
    {
        /// <summary>
        /// 执行sql文本
        /// </summary>
        public string SqlText { get; set; }

        /// <summary>
        /// 实体对象
        /// </summary>
        public object Model { get; set; }

        public TransactionItem() { 
        
        }

        public TransactionItem(string sqlText,object model)
        {
            this.SqlText = sqlText;
            this.Model = model;
        }
    }
}
